package com.test.myfreemark;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;


public class DBUtils {
		private static Connection con;
		public static String DRIVERSELSERVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";//sqlserver
		public static String DRIVERMYSQL = "com.mysql.jdbc.Driver";//mysql
		public static String DRIVERORACLE = "oracle.jdbc.OracleDriver";//oracle
		public static String DRIVER = DRIVERSELSERVER;//默认sqlserver
		public static String USER = "sa";
		public static String PASS = "1234";
		public static String URL = "jdbc:sqlserver://127.0.0.1:1433;databaseName=renbanghome;";//sqlservel
		public static String tableNamesForSQLSERVER = "select name from sys.tables";//sqlserver
		//Mysql表名
		public static String tableNamesForMYSQL = "select table_name as 'name' from information_schema.tables where table_schema='";
		public static String tableNamesForORACLE = "SELECT TABLE_NAME as name FROM USER_TABLES";//oracle
		public static String tableNamesSQL = tableNamesForSQLSERVER;//默认sqlservel
		//mysql和sqlserver列名
		public static String TableClumsSAndM = "SELECT DATA_TYPE,COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='";
		public static String TableClumsORA = "select  DATA_TYPE,COLUMN_NAME from all_tab_columns where TABLE_NAME='";//oracle
		public static String TableClums = TableClumsSAndM;//默认mysql和sqlserver
		//		
//		private static String URL="jdbc:mysql://127.0.0.1:3306/chen";//mysql
//		private static String URL="jdbc:oracle:thin:@127.0.0.1:1521:XE";//oracle
		//单例连接，不需要更换数据库的情况用
		public static 	Connection  getSigConnection() throws Exception{
			if(con==null){
				Class.forName(DRIVER);
				con=DriverManager.getConnection(URL, USER, PASS);	
				}
			return con;
		}
		
		//需要更换数据库的情况用
		public static 	Connection  getMunConnection() throws Exception{
			Class.forName(DRIVER);
			con=DriverManager.getConnection(URL, USER, PASS);	
			return con;
		}
		
		public static 	void  closeDB(Connection con,Statement stmt,ResultSet rs) throws Exception{
			if(rs!=null)
			rs.close();
			if(stmt!=null)
			stmt.close();
			if(con!=null)
			con.close();
		}
		
		public static List<String> getTableNames(String database) throws Exception{
			Connection con = getMunConnection();
			System.out.println(con);
			Statement stmt = con.createStatement();
			if(database!=null){
				stmt.execute("use "+database);
			}
			ResultSet rs = stmt.executeQuery(tableNamesSQL);
			List <String> TableNames = new ArrayList<String>();
			while(rs.next()){
				TableNames.add(rs.getString("name"));
			}
			closeDB(con,stmt,rs);
			return TableNames;
		}
		
		
		public static List<Map<String,String>> getTableClums(String tableName) throws Exception{
			Connection con = getMunConnection();
			Statement stmt = con.createStatement();
			String sql = TableClums+tableName+"'" ;
			ResultSet rs = stmt.executeQuery(sql);
			List <Map<String,String>> TableNames = new ArrayList<Map<String,String>>();
			while(rs.next()){
				Map <String,String> map = new HashMap<String, String>();
				map.put("DATA_TYPE", formatType(rs.getString("DATA_TYPE")));
				map.put("COLUMN_NAME", rs.getString("COLUMN_NAME"));
				TableNames.add(map);
			}
			closeDB(con,stmt,rs);
			return TableNames;
		}
		
		public static void main(String[] args) throws Exception {
			
			DBUtils.DRIVER = DBUtils.DRIVERORACLE;
//			DBUtils.URL = "jdbc:mysql://127.0.0.1:3306/chen";
			DBUtils.URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
			DBUtils.USER = "scott";
			DBUtils.PASS = "tiger";
			DBUtils.tableNamesSQL = DBUtils.tableNamesForORACLE;
			System.out.println(getTableNames(null));
//			DBUtils.TableClums = DBUtils.TableClumsORA;
			System.out.println(getMunConnection());
			
			
//			 System.out.println(UUID.randomUUID().toString().replace("-", ""));
//			 List<String> TableNames =	getTableNames("chen");
//			 for (String tableName : TableNames) {
//				 System.out.println(tableName+"表字段:");
//				 List<Map<String,String>> TableColums = getTableClums(tableName);
//				 for (Map<String, String> Colum : TableColums) {
//					System.out.println(Colum);
//				}
//			}
			
		}
		
		public static String formatType(String value){
			switch (value) {
			case "int":
			case "number":
			return "Integer";	
			case "vachar":
			case "text":
			return "String";
			case "date":
			case "datetime":
			return "Date";
			default:
			return "String";
			}
		}
		
}
